using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Linq;
using NHibernate.Transform;

namespace Infrastucture
{
    public class SpkRepository : BaseNHibernateRepository<Spk>
    {
        public SpkRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
        }

        public string GetMaxNumber(string kodeAwal)
        {
            string res = string.Empty;
            var lenKode = kodeAwal.Length + 10;
            var query = Session.CreateSQLQuery("Select Max(NoSPK) as Number from SPK  where NoSPK like'" + kodeAwal + "%' AND  Len(NoSPK)=" + lenKode).AddScalar("Number", NHibernateUtil.String);
            var objRes = query.List()[0];
            if (objRes != null) res = objRes.ToString();
            return res;
        }

        public override IList<Spk> GetDataPaged(int page, int pageSize, string sortProperty, string sortType)
        {
            var Results = new List<Spk>();
            using (ITransaction tx = Session.BeginTransaction())
            {

                StringBuilder sb = new StringBuilder();
                sb.Append("Select spk.NoSPK,spk.SpkDate,spk.CustomerName,spk.Keterangan,si.SINo,spk.Status,do.DONo ");
                sb.Append("from SPK spk  ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.NoSPK = spk.NoSPK ");
                sb.Append("left Join SalesInvoice si on si.DONo=do.DONo ");
                sb.AppendFormat("Order By spk.{0} {1} ", sortProperty, sortType);

                var query = Session.CreateSQLQuery(sb.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(Spk)))
                                   ;

                var count = Session.CreateSQLQuery("Select Count(*) as recCount from SalesOrder").AddScalar("recCount", NHibernateUtil.Int64);
                Results = query.List<Spk>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            //RecordCount = Results.TotalCount;
            return Results;
        }


        public override IList<Spk> GetDataPaged(int page, int pageSize)
        {
            var Results = new List<Spk>();
            using (ITransaction tx = Session.BeginTransaction())
            {

                StringBuilder sb = new StringBuilder();
                sb.Append("Select spk.NoSPK,spk.SpkDate,spk.CustomerName,spk.Keterangan,si.SINo,spk.Status,do.DONo ");
                sb.Append("from SPK spk  ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.NoSPK = spk.NoSPK ");
                sb.Append("left Join SalesInvoice si on si.DONo=do.DONo ");

                var query = Session.CreateSQLQuery(sb.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(Spk)))
                                   ;

                var count = Session.CreateSQLQuery("Select Count(*) as recCount from SPK").AddScalar("recCount", NHibernateUtil.Int64);
                Results = query.List<Spk>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            //RecordCount = Results.TotalCount;
            return Results;
        }


        public void UpdateEmailStatus(string NoSPK)
        {
            ISQLQuery command =
            Session.CreateSQLQuery("Update SPK set HasSent=1 Where NoSPK='" + NoSPK + "'");
            int res = command.ExecuteUpdate();
        }

        //public override IList<Spk> GetDataPaged(Dictionary<String, Object> criteria, string expression, int page, int pageSize)
        //{
        //    IList<Spk> Results = new List<Spk>();
        //    using (var tx = Session.BeginTransaction())
        //    {

        //        StringBuilder sb2 = new StringBuilder();
        //        int i = 0;
        //        var filter = new List<string>();
        //        foreach (var criterion in criteria)
        //            if (!typeof(Spk).GetProperties().Any(x => x.Name == criterion.Key)) filter.Add(criterion.Key);

        //        filter.ForEach((x) =>
        //        {
        //            criteria.Remove(x);
        //        }
        //            );

        //        if (criteria.Count > 0) sb2.Append(" Where ");

        //        foreach (var criterion in criteria)
        //        {
        //            if (criterion.Value == null) continue;
        //            if (criterion.Value.GetType() == typeof(string))
        //                sb2.AppendFormat(" spk." + criterion.Key + " like'%{0}%' ", criterion.Value.ToString());
        //            else
        //                sb2.AppendFormat(" spk." + criterion.Key + "='{0}' ", criterion.Value);

        //            i += 1;
        //            if (i < criteria.Count)
        //                sb2.Append(expression);
        //        }


        //        StringBuilder sb = new StringBuilder();
        //        sb.Append("Select spk.NoSPK,spk.SpkDate,spk.CustomerName,spk.Keterangan,si.SINo,spk.Status,do.DONo ");
        //        sb.Append("from SPK spk  ");
        //        sb.Append("LEFT JOIN DeliveryOrder do ON do.NoSPK = spk.NoSPK ");
        //        sb.Append("left Join SalesInvoice si on si.DONo=do.DONo ");

        //        var query = Session.CreateSQLQuery(sb.ToString() + sb2.ToString())
        //                           .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
        //                           .SetResultTransformer(Transformers.AliasToBean(typeof(Spk)))
        //                           ;
        //        StringBuilder sb3 = new StringBuilder();
        //        sb3.Append("Select Count(*) ");
        //        sb.Append("Select spk.NoSPK,spk.SpkDate,spk.CustomerName,spk.Keterangan,si.SINo,spk.Status,do.DONo ");
        //        sb.Append("from SPK spk  ");
        //        sb.Append("LEFT JOIN DeliveryOrder do ON do.NoSPK = spk.NoSPK ");
        //        sb.Append("left Join SalesInvoice si on si.DONo=do.DONo ");
        //        var count = Session.CreateSQLQuery(sb3.ToString() + sb2.ToString()).AddScalar("recCount", NHibernateUtil.Int64);
        //        Results = query.List<Spk>().ToList();

        //        RecordCount = (long)((IList)count.List())[0];
        //        tx.Commit();
        //    }

        //    return Results;
        //}
    }
}